package com.hz.control;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import com.hz.dbconn.DBConnect;
import com.hz.model.Service;
public class ServiceControl {
    
    /*
     * 批量增加时长
     */
    public static boolean AddServices(Collection<Service> slist) {
		DBConnect dbc = new DBConnect();
		String sql = "insert into service(userId,actId,serviceHours,behavior,duty,inUsing) values(?,?,?,?,?,?)";
		PreparedStatement pstmt = null;
		Iterator<Service> iterator = slist.iterator();
		dbc.setAutoCommit(false);
		dbc.prepareStatement(sql);
		pstmt = dbc.getPreparedStatement();
		try{
		     while(iterator.hasNext()){
			 Service s = (Service)iterator.next();
		        int index = 1; 
		        pstmt.setInt(index++, s.getUserid());
		        pstmt.setInt(index++, s.getActId());
		        pstmt.setFloat(index++, (float) s.getServicesHours());
		        pstmt.setInt(index++, s.getBehavior());
		        pstmt.setString(index++, s.getDuty());
		        pstmt.setInt(index++, 1);
		        pstmt.addBatch();
		        if(!updateVolLev(s.getUserid(),setVolLevByHours(s.getUserid(),s.getServicesHours()))){
		        	System.out.println("同步更新等级失败!");
		        	return false;
		        }
		     }
		        pstmt.executeBatch();
		        dbc.commit();
		}catch(SQLException e){
		        e.printStackTrace();
			System.out.println("批量增加时长插入失败！\n");
			return false;
		}finally{
			dbc.close();
		}
		
		return true;
		
		
	}
    /*
     * 获取某个人某项活动的时长
     * 参数（活动id，用户Id）
     */
    public static float getHours(int actid,int uid) {
	DBConnect dbc = new DBConnect();
	String sql = "select serviceHours from service where userId='"+uid+"' and actId = '"+actid+"'";
	float hours = 0;
	dbc.prepareStatement(sql);
	try {
	    ResultSet rs = dbc.executeQuery();
	    if(rs.next()){
		hours = rs.getFloat("serviceHours");
	    }
	    return hours;
	} catch (Exception e) {
	    // TODO: handle exception
	    e.printStackTrace();
	    System.out.println("查找时长失败！");
	    return hours;
	}finally{
		dbc.close();
	}
	
    }
    
    /*
     * 自动更新星级
     */
    
    public static boolean updateVolLev(int uid,int vollev){
    	DBConnect dbc = new DBConnect();
    	String sql = "update user set volunteerLev = '"+vollev+"' where id ='"+uid+"'";
    	System.out.println(sql);
    	dbc.prepareStatement(sql);
    	try {
			dbc.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("更新志愿者等级错误");
			return false;
		}finally{
			dbc.close();
		}
    	
    
    	
    }
    
    
    /**
     * 获取志愿者服务总时长
   *uid 用户id
   * inUsing现在是否使用，0不使用，1使用，大于1的都计算
   */
    public static float getTotalHours(int uid,int use){
    	float hours =0;
    	DBConnect dbc = new DBConnect();
    	String sql="";
    	sql = use>1? "select sum(serviceHours)from service where userId ='"+uid+"'": "select sum(serviceHours)from service where userId ='"+uid+"' and inUsing='"+use+"'";
    	dbc.prepareStatement(sql);
    	try {
			ResultSet rSet = dbc.executeQuery();
			if(rSet.next()){
			    hours = rSet.getFloat(1);
//			    System.out.println(hours);
             }
			return hours;
    	}catch (Exception e) {
    		e.printStackTrace();
			// TODO: handle exception
    		System.out.println("获取总时长失败");
		}finally{
			dbc.close();
		}
		return hours;
    	}
    
    
    /**
     * 获取志愿者服务总时长
   *uid 用户id
   * inUsing现在是否使用，0不使用，1使用，大于1的都计算
   */
    public static float getTotalHoursByYear(int uid,int use,String year){
    	float hours =0;
    	DBConnect dbc = new DBConnect();
    	year = year.equals("0")?"%":year;
    	String sql="";
    	sql = use>1? "select sum(serviceHours)from service,activity where service.userId ='"+uid+"'" : "select sum(serviceHours)from service,activity where service.userId ='"+uid+"' and service.inUsing='"+use+"'";
    	sql+=" and activity.id=service.actId and activity.startTime like '"+year+"%'";
    	dbc.prepareStatement(sql);
    	System.out.println(sql);
    	try {
			ResultSet rSet = dbc.executeQuery();
			if(rSet.next()){
			    hours = rSet.getFloat(1);
//			    System.out.println(hours);
             }
			return hours;
    	}catch (Exception e) {
    		e.printStackTrace();
			// TODO: handle exception
    		System.out.println("获取总时长失败");
		}finally{
			dbc.close();
		}
		return hours;
    	}
    /*
     * 根据服务总时间划分等级
     */
    public static int setVolLevByHours(int uid,float newhours){
    	float hours =0;
    	DBConnect dbc = new DBConnect();
    	String sql = "select sum(serviceHours)from service where userId ='"+uid+"' and inUsing='1'";
    	dbc.prepareStatement(sql);
    	try {
			ResultSet rSet = dbc.executeQuery();
			if(rSet.next()){
			    hours = rSet.getFloat(1);
			    System.out.println(hours);
			}
			System.out.println((int) (hours/20));
			 return (int) ((hours+newhours)/20);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("查找单个人服务总时数失败");
			return (int) (hours/20);
		}finally{
			dbc.close();
		}
		
    }
    /*
     * 查找某个用户参与的所有活动
     */
    public static Collection<Service> oneGetAllActivities(int uid) {
        DBConnect dbc = new DBConnect();
        Collection<Service> slist = new ArrayList<Service>();
        String sql = "select * from service where userId = '"+uid+"'";
        ResultSet rs = null;
        dbc.prepareStatement(sql);
		try {  
		       rs = dbc.executeQuery();
			while (rs.next()) {
			        Service service = new Service();
			        service.setActId(rs.getInt("actId"));
			        service.setId(rs.getInt("id"));
			        service.setBehavior(rs.getInt("behavior"));
			        service.setServicesHours(rs.getFloat("serviceHours"));
			        service.setUserid(rs.getInt("userId"));
			        service.setDuty(rs.getString("duty"));
			        slist.add(service);
			}
			return slist;

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
		    dbc.close();
		}
	    
	}
    
    /*
     * 查找某个用户参与的所有活动
     */
    public static Collection<Service> oneGetActivitiesByYears(int uid,String year) {
        DBConnect dbc = new DBConnect();
        Collection<Service> slist = new ArrayList<Service>();
        if(year.equals("0")){
        	year="%";
        }
        String sql = "select * from service,activity where service.userId = '"+uid+"' and " +
        		" activity.id=service.actId and activity.startTime like '"+year+"%'";
        ResultSet rs = null;
        dbc.prepareStatement(sql);
        System.out.println(sql);
		try {  
		       rs = dbc.executeQuery();
			while (rs.next()) {
			        Service service = new Service();
			        service.setActId(rs.getInt("actId"));
			        service.setId(rs.getInt("id"));
			        service.setBehavior(rs.getInt("behavior"));
			        service.setServicesHours(rs.getFloat("serviceHours"));
			        service.setUserid(rs.getInt("userId"));
			        service.setDuty(rs.getString("duty"));
			        slist.add(service);
			}
			return slist;

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
		    dbc.close();
		}
	    
	}
    
    public static boolean ChageInUsing(){
	DBConnect dbConnect = new DBConnect();
	boolean flag = false;
	String sql = "update service set inUsing = '0' where inUsing='1'";
	dbConnect.prepareStatement(sql);
	try {
	    if(dbConnect.executeUpdate()){
		 flag = true;
	    }
	} catch (Exception e) {
	    // TODO: handle exception
	    e.printStackTrace();
	    System.out.println("ChangeInUsing ERROR");
	}finally{
	    dbConnect.close();
	}
	return flag;
    }
    public static void main(String args[]){
    	System.out.println(updateVolLev(2,setVolLevByHours(2,3)));
    }

}
